
import pandas as pd 
import numpy as np
import time
import cx_Oracle
from sqlalchemy import create_engine

#检验中英文名称映射字典是否全全面
def check_na(ser,dic_eng_china):#传入被映射的表 返回的df中如果有nan则要 重新更新字典
    ser = pd.Series(ser)#可以包含df.columns
    ser2 = ser.map(dic_eng_china)
    df = pd.concat([ser,ser2],axis=1)
    df.columns = ['before','after']
    if len(df.loc[df.after.isna()]) != 0:
        print(df.loc[df.after.isna()])
    return df
    
    
    

#指标得分计算函数
def get_index_score(dfs,dic_eng_china,dic_index_map):
    dfrs1 = dfs.stack().reset_index().set_index('rating_id') #.map(dic_eng_china)
    dfrs1.columns = ['index_name','index_score']
    print('开始中英文映射')
    dfrs1.index_name = dfrs1.index_name.map(dic_eng_china)
    df = check_na(dfrs1.index_name,dic_index_map)#检验map后是否出现空值 即字典是否有问题
    print('开始映射分组')
    dfrs1.index_name = dfrs1.index_name.map(dic_index_map)
    print('开始计算指标得分')
    df_result = dfrs1.reset_index().groupby(['rating_id','index_name'])['index_score'].sum().reset_index()
    df_result['esg_time'] = time.strftime('%Y%m%d %H:%M:%S',time.localtime())
    return df_result
    
    
    

#计算因子/特征分数窄表
def get_factor_score(dfs,dic_eng_china):
    dfrs1 = dfs.stack().reset_index().set_index('rating_id') #.map(dic_eng_china
    dfrs1.columns = ['factor_name','factor_score']
    dfrs1.factor_name = dfrs1.factor_name.map(dic_eng_china)
    dfrs1 = dfrs1.reset_index()
    dfrs1['esg_time'] = time.strftime('%Y%m%d %H:%M:%S',time.localtime())
    return dfrs1




# 获取ESG等级
def get_level2(result):
    '''
    输入esg分数表
    返回增加了esg等级的esg分数表
    '''
    result['rank_'] = result.groupby('model_type').esg_score.rank(ascending=False,pct=True)
    
    result['esg_rating'] = 1
    result.loc[result.rank_ <= 0.8,'esg_rating'] = 2
    result.loc[result.rank_ <= 0.6,'esg_rating'] = 3

    result.loc[result.rank_ <= 0.4,'esg_rating'] = 4
    result.loc[result.rank_ <= 0.2,'esg_rating'] = 5
    result.loc[result.rank_.isna(),'esg_rating'] = 0
    
    result.esg_rating = result.esg_rating.map({5:'A',4:'B',3:'C',2:'D',1:'E',0:'M'})
    del result['rank_']
    result = result.reset_index(drop = True)
    return result 
    


from scipy.stats import norm
def get_level(result):
    print('开始计算等级')
    result['esg_rating'] = 'M'
    for model_type in result.model_type.unique():
        if model_type in ['ytb','yts','ntb','nts']:
                mean = result[result.model_type == model_type].esg_score.mean()
                std = result[result.model_type == model_type].esg_score.std()
                bin_edges = [norm.ppf(i/5, mean, std) for i in range(6)]
                print('{}分箱边缘：'.format(model_type),bin_edges)
                result.loc[result.model_type == model_type,'esg_rating'] = pd.cut(result[result.model_type == model_type]['esg_score'], bin_edges, labels=['A', 'B', 'C', 'D', 'E'])
#                 plt.figure(figsize = (8,6))
#                 result[result.model_type == model_type]['esg_rating'].value_counts().sort_index().plot(kind='bar')
    result.esg_rating.fillna('M',inplace=True)
    result = result.reset_index(drop = True)
    print('等级已经计算完成')
    return result


def return_allcustomer(df_esg_result,dfjch2):              #  传入评价企业的分数表与分群后的基础信息 
    print('在分数上拼接基础信息表的信息与模型类型')
    df_result = pd.merge(df_esg_result,dfjch2[['rating_id','model_type','industry_code']],on='rating_id',how='left')    #评价客户的结果
    print('获取不评价客户')
    df_unevalate = dfjch2.loc[~dfjch2.rating_id.isin(df_result.rating_id)]                                          #不评价客户
    print('{}条不评价客户与{}评价客户拼接'.format(len(df_unevalate),len(df_result)))
    dfrs2 = pd.concat([df_result,df_unevalate])             #返回全量企业
    dfrs2.rename(columns={'id_number':'enterprise_cert_no'},inplace=True)
    dfrs2['esg_time'] = time.strftime('%Y%m%d %H:%M:%S',time.localtime())
    return dfrs2 
 
 

#静态填充
def fill_jinzong_score_static(dfs_conn_add,df,dfs_fill):
    df_conn = df.reset_index()[['id_number','model_type']]                   #选取信用代码为联接建
    dfs_new = pd.merge(dfs_conn_add,df_conn,on='id_number',how = 'left')     #已经联接完模型类型 金综小分表    
    for col in ['e_score','s_score','g_score']:
            for mt in ['ntb','nts','ytb','yts']:
                if mt in dfs_new.model_type.values:                          #有这个类型才进行填充
                    dfs_new.loc[(dfs_new[col].isna()) & (dfs_new.model_type == mt),col] = dfs_fill.loc[dfs_fill.model_type == mt,col].values[0]
    del dfs_new['model_type']
    return dfs_new      

#添加金综小分数
def add_score(dfs,df,df_add_score,dic_EngFeature_dim,dfs_fill):                                                #传入特征分数表 特征表  金综小分表 esg分组字典
    print('初步聚合e,s,g三个纬度的分数')
    dfs[['E','G','S']] = dfs.groupby(dic_EngFeature_dim,axis=1).sum()                                 #计算三个维度分数
    print('添加联接键')
    df_conn = df.reset_index()[['rating_id','id_number']]                                            #获取id_number 连接键
    dfs_conn = pd.merge(dfs,df_conn,on='rating_id',how = 'left')                                     #加上社会信用代码 左连接
    print('联接金综分数表')
    if ('s_socre' in df_add_score.columns) and ('s_score' not in df_add_score.columns):              #如果名字错误则修正
        df_add_score.rename(columns={'s_socre':'s_score'},inplace=True)
    dfs_conn_add = pd.merge(dfs_conn,df_add_score,right_on='uniscid',left_on='id_number',how='left') #连接dfs与金综小分表        
    lst_nan = dfs_conn_add.loc[dfs_conn_add.e_score.isna()].id_number.to_list()                      #获取缺少金综分数的数据索引
    print('开始金综分数填充')
    dfs_conn_add = fill_jinzong_score_static(dfs_conn_add,df,dfs_fill)                                               #金综分数均值填充金综缺失的分数
    print('{}家企业没有金综分数需要单独分析'.format(len(lst_nan)))
    print('二次聚合e,s,g三个纬度的分数')
    dfs_conn_add.e_score = dfs_conn_add.E + dfs_conn_add.e_score                                      #计算三个维度分数
    dfs_conn_add.s_score = dfs_conn_add.S + dfs_conn_add.s_score
    dfs_conn_add.g_score = dfs_conn_add.G + dfs_conn_add.g_score
    print('删掉无意义特征，分数与lst_nan已返回')
    dfs_conn_add.drop(['E','S','G','uniscid','cust_type'],axis=1,inplace=True)                        #删掉无意义特征
    return dfs_conn_add[['rating_id','id_number','e_score','s_score','g_score']],lst_nan                      #返回三个维度的分数                                                               #返回未调整的三个维度的分数 


